﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Module;
using System.Data.SqlClient;
using Qmaker.DAL;
using System.Data;

namespace DLL
{
    public class DUserRelation
    {
        #region SQL语句
        private const string SQL_INSERT = "insert into UserRelation(UserRelationId,NormalUserPassportId,AngelUserPassportId,Relationtype,Status,DateCreated) values (@UserRelationId,@NormalUserPassportId,@AngelUserPassportId,@Relationtype,@Status,@DateCreated)";

        private const string SQL_UPDATE = "update UserRelation set NormalUserPassportId=@NormalUserPassportId,AngelUserPassportId=@AngelUserPassportId,Relationtype=@Relationtype,Status=@Status,DateCreated=@DateCreated where UserRelationId=@UserRelationId";

        private const string SQL_DELETE = "delete from UserRelation where UserRelationId=@UserRelationId ";

        private const string SQL_DELETE_NORMALID = "delete from UserRelation where NormalUserPassportId=@NormalUserPassportId ";

        private const string SQL_DELETE__ANGELID = "delete from UserRelation where AngelUserPassportId=@AngelUserPassportId ";

        private const string SQL_SELECT_NORMALID = "select UserRelationId,NormalUserPassportId,AngelUserPassportId,Relationtype,Status,DateCreated from UserRelation where NormalUserPassportId=@NormalUserPassportId";

        private const string SQL_SELECT_ANGELID = "select UserRelationId,NormalUserPassportId,AngelUserPassportId,Relationtype,Status,DateCreated from UserRelation where AngelUserPassportId=@AngelUserPassportId";

        private const string SQL_SELECT_ALL = "select UserRelationId,NormalUserPassportId,AngelUserPassportId,Relationtype,Status,DateCreated from UserRelation where Status = 1";

        private const string SQL_SELECT_BY_ID = "select UserRelationId,NormalUserPassportId,AngelUserPassportId,Relationtype,Status,DateCreated from UserRelation where UserRelationId = @UserRelationId and Status = 1";

        private const string SQL_SELECT_BY_PASSPORTID = "select UserRelationId,NormalUserPassportId,AngelUserPassportId,Relationtype,Status,DateCreated from UserRelation where NormalUserPassportId = @NormalUserPassportId and AngelUserPassportId = @AngelUserPassportId and Status = 1";
                
        #endregion

        #region 公共接口
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="userRelationInfo"></param>
        /// <returns></returns>
        public bool Insert(UserRelationInfo userRelationInfo)
        {
            if (userRelationInfo == null)
                throw new ArgumentNullException("userRelationInfo");

            SqlParameter[] parms = GetInsertParms(userRelationInfo);
            //parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="userRelationInfo"></param>
        /// <returns></returns>
        public bool Update(UserRelationInfo userRelationInfo)
        {
            if (userRelationInfo == null || userRelationInfo.NormalUserPassportId.ToString().Length != 18)
                throw new ArgumentNullException("userRelationInfo");

            SqlParameter[] parms = GetInsertParms(userRelationInfo);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 根据主键删除
        /// </summary>
        /// <param name="userRelationId"></param>
        /// <returns></returns>
        public int Delete(long userRelationId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@UserRelationId",SqlDbType.BigInt)
                };
            parms[0].Value = userRelationId;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE, parms);

        }
        /// <summary>
        /// 根据自强会员Id删除
        /// </summary>
        /// <param name="normalUserID"></param>
        /// <returns></returns>
        public int DeleteByNormalId(long normalUserID)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@NormalUserPassportId",SqlDbType.BigInt)
                };
            parms[0].Value = normalUserID;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE_NORMALID, parms);

        }
        /// <summary>
        /// 根据天使会员Id删除
        /// </summary>
        /// <param name="angelUserID"></param>
        /// <returns></returns>
        public int DeleteByAngellId(long angelUserID)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@AngelUserPassportId",SqlDbType.BigInt)
                };
            parms[0].Value = angelUserID;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE__ANGELID, parms);

        }
        /// <summary>
        /// 根据自强会员Id获取关系列表
        /// </summary>
        /// <param name="normalUserID"></param>
        /// <returns></returns>
        public List<UserRelationInfo> GetAllRelationByNormalId(long normalUserID)
        {
            List<UserRelationInfo> relations = new List<UserRelationInfo>();

            UserRelationInfo userRelationInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@NormalUserPassportId",SqlDbType.BigInt)
                };
            parms[0].Value = normalUserID;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_NORMALID, parms))
            {
                while (reader.Read())
                {
                    userRelationInfo = GetInstanceByDataReader(reader);
                    if (userRelationInfo != null && userRelationInfo.NormalUserPassportId != 0)
                        relations.Add(userRelationInfo);
                }
            }
            return relations;
        }

        /// <summary>
        /// 根据天使会员Id获取关系列表
        /// </summary>
        /// <param name="angelUserID"></param>
        /// <returns></returns>
        public List<UserRelationInfo> GetAllRelationByAngelId(long angelUserID)
        {
            List<UserRelationInfo> relations = new List<UserRelationInfo>();

            UserRelationInfo userRelationInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@AngelUserPassportId",SqlDbType.BigInt)
                };
            parms[0].Value = angelUserID;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ANGELID, parms))
            {
                while (reader.Read())
                {
                    userRelationInfo = GetInstanceByDataReader(reader);
                    if (userRelationInfo != null && userRelationInfo.NormalUserPassportId != 0)
                        relations.Add(userRelationInfo);
                }
            }
            return relations;
        }

        public List<UserRelationInfo> GetAllRelationInfo()
        {
            List<UserRelationInfo> relations = new List<UserRelationInfo>();
            UserRelationInfo userRelationInfo = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ALL, null))
            {
                while (reader.Read())
                {
                    userRelationInfo = GetInstanceByDataReader(reader);
                    if (userRelationInfo != null && userRelationInfo.NormalUserPassportId != 0)
                        relations.Add(userRelationInfo);
                }
            }
            return relations;
        }

        public UserRelationInfo GetRelationInfoByID(long userRelationId)
        {
            UserRelationInfo userRelationInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@UserRelationId",SqlDbType.BigInt)
                };
            parms[0].Value = userRelationId;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_BY_ID, parms))
            {
                while (reader.Read())
                {
                    userRelationInfo = GetInstanceByDataReader(reader);
                    break;
                }
            }
            return userRelationInfo;
        }

        public UserRelationInfo GetRelationInfoByPassportId(string nomalUserPassportId, string angelUserPassportId)
        {
            UserRelationInfo userRelationInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@NormalUserPassportId",SqlDbType.BigInt),
                    new SqlParameter("@AngelUserPassportId",SqlDbType.BigInt),
                };
            parms[0].Value = nomalUserPassportId;
            parms[1].Value = angelUserPassportId;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_BY_PASSPORTID, parms))
            {
                while (reader.Read())
                {
                    userRelationInfo = GetInstanceByDataReader(reader);
                    break;
                }
            }
            return userRelationInfo;
        }

        #endregion

        #region 私有接口

        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] parms)
        {
            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i].Value == null)
                {
                    parms[i].Value = DBNull.Value;
                }
                if (parms[i].DbType == DbType.DateTime && parms[i].Value.Equals(DateTime.MinValue))
                {
                    parms[i].Value = DBNull.Value;
                }
            }
            return parms;
        }

        private SqlParameter[] GetInsertParms(UserRelationInfo model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserRelationId", SqlDbType.BigInt,8),
					new SqlParameter("@NormalUserPassportId", SqlDbType.BigInt,8),
					new SqlParameter("@AngelUserPassportId", SqlDbType.BigInt,8),
					new SqlParameter("@Relationtype", SqlDbType.Int,4),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@DateCreated", SqlDbType.DateTime)};
            parameters[0].Value = model.UserRelationId;
            parameters[1].Value = model.NormalUserPassportId;
            parameters[2].Value = model.AngelUserPassportId;
            parameters[3].Value = model.Relationtype;
            parameters[4].Value = model.Status;
            parameters[5].Value = model.DateCreated;

            return SetInsertParmsCheck(parameters);
        }

        private UserRelationInfo GetInstanceByDataReader(SqlDataReader reader)
        {
            UserRelationInfo current = new UserRelationInfo();
            //UserRelationId,NormalUserPassportId,AngelUserPassportId,Relationtype,Status,DateCreated
            current.UserRelationId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0);
            current.NormalUserPassportId = reader.IsDBNull(1) ? 0 : reader.GetInt64(1);
            current.AngelUserPassportId = reader.IsDBNull(2) ? 0 : reader.GetInt64(2);
            current.Relationtype = reader.IsDBNull(3) ? 0 : reader.GetInt32(3);
            current.Status = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
            current.DateCreated = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5);

            return current;
        }

        #endregion

    }
}
